package com.huitone.smspfm.dao.oracle;

import java.lang.reflect.ParameterizedType;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

import com.bin.common.utils.StringUtils;


public abstract class BaseDao<T> {
	
	public JdbcTemplate jdbcTemplate;
	
	public SimpleJdbcInsert simpleJdbcInsert;
	
	protected String tableName;
	
	protected Class<T> objMapper;
	
	/**
	 * 获取表格的所有数据
	 * @return 返回Map列表
	 */
	public List<Map<String, Object>> getAll() {
		return jdbcTemplate.queryForList("SELECT * FROM " + tableName);
	}
	
	/**
	 * 分页查询,返回Map列表
	 * @param offset 偏移量从零开始计算
	 * @param limit  每页记录数
	 * @return
	 */
	public List<Map<String, Object>> getPage(long offset, long limit) {
		return jdbcTemplate.queryForList("SELECT * FROM  (SELECT A.*, ROWNUM RN FROM (SELECT * FROM " + tableName 
				+ ") A WHERE ROWNUM <= " + (offset + limit) + ") LEFT JOIN (SELECT COUNT(1) AS total FROM " + tableName + ") ON 1 = 1 WHERE RN >= " + (offset + 1));
	}
	
	/**
	 * 获取表格的所有数据
	 * @return 返回对象列表
	 */
	public List<T> getAllObj() {
		return jdbcTemplate.query("SELECT * FROM " + tableName, new BeanPropertyRowMapper<T>(getTClass()));
	}
	
	/**
	 * 分页查询,返回对象列表(使用该方法时,表字段不能以"_"开头)
	 * @param offset 偏移量从零开始计算
	 * @param limit  每页记录数
	 * @return
	 */
	public List<T> getPageObj(long offset, long limit) {
		return jdbcTemplate.query("SELECT * FROM  (SELECT A.*, ROWNUM RN FROM (SELECT * FROM " + tableName 
				+ ") A WHERE ROWNUM <= " + (offset + limit) + ") LEFT JOIN (SELECT COUNT(1) AS total FROM " + tableName + ") ON 1 = 1 WHERE RN >= " + (offset + 1), new BeanPropertyRowMapper<T>(getTClass()));
	}
	
	/**
	 * 查询表格是否已经存在
	 * @return
	 */
	public boolean isTableExists() {
		return jdbcTemplate.queryForList("SELECT table_name FROM user_tables WHERE table_name='" + StringUtils.upperCase(tableName) + "'").size() > 0 ? true : false;
	}
	
	/**
	 * 查询表格是否已经存在
	 * @param tableName
	 * @return
	 */
	public boolean isTableExists(String tableName) {
		return jdbcTemplate.queryForList("SELECT table_name FROM user_tables WHERE table_name='" + StringUtils.upperCase(tableName) + "'").size() > 0 ? true : false;
	}
	
	/**
	 * 查询触发器是否存在
	 * @param triggerName
	 * @return
	 */
	public boolean isTriggerExists(String triggerName) {
		return jdbcTemplate.queryForList("SELECT trigger_name FROM user_triggers WHERE trigger_name='" + StringUtils.upperCase(triggerName) + "'").size() > 0 ? true : false;
	}
	
	public boolean isTriggerExists() {
		return jdbcTemplate.queryForList("SELECT trigger_name FROM user_triggers WHERE trigger_name='" + StringUtils.upperCase(tableName) + "DEL_TRIGGER'").size() > 0 ? true : false;
	}
	
	/**
	 * 创建表
	 * @param sql 建表语句
	 * @return
	 */
	public boolean createTable(String tableSql) {
		return excute(tableSql);
	}
	
	/**
	 * 创建触发器
	 * @param triggerSql
	 * @return
	 */
	public boolean createTrigger(String triggerSql) {
		return excute(triggerSql);
	}
	
	/**
	 * 执行SQL语句
	 * @param sql
	 * @return
	 */
	public boolean excute(String sql) {
		
		boolean result = true;
		
		try {
			jdbcTemplate.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}
		
		return result;
	}
	
	@SuppressWarnings("unchecked")
	private Class<T> getTClass() {
		return (Class<T>) ((ParameterizedType)getClass().getGenericSuperclass()).getActualTypeArguments()[0];
	}
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName(tableName);
		this.jdbcTemplate = jdbcTemplate;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		
		if (simpleJdbcInsert != null && StringUtils.isEmpty(simpleJdbcInsert.getTableName())) {
			simpleJdbcInsert.setTableName(tableName);
		}
		
		this.tableName = tableName;
	}

	public Class<T> getObjMapper() {
		return objMapper;
	}

	public void setObjMapper(Class<T> objMapper) {
		this.objMapper = objMapper;
	}
}
